package com.mindata.blockchain.core.repository;

import com.mindata.blockchain.core.bean.SelectCountResult;
import com.mindata.blockchain.core.model.MessageEntity;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author wuweifeng wrote on 2017/10/25.
 */
public interface MessageRepository extends BaseRepository<MessageEntity> {
    /**
     * 删除一条记录
     * @param messageId  messageId
     */
    void deleteByMessageId(String messageId);

    /**
     * 查询一个
     * @param messageId messageId
     * @return MessageEntity
     */
    MessageEntity findByMessageId(String messageId);

    @Query(value="select * from message limit ?1,?2",nativeQuery = true)
    List<MessageEntity> findAllByLim(Integer page,Integer size);


    @Query(value = "select count(1) from message",nativeQuery = true)
    int getCount();




    /*@Query(value = "SELECT new com.mindata.blockchain.common.SelectCountResult( count1,day,type FROM (SELECT count(*) as count1,ct as day,'xx' as type  from (select strftime('%Y-%m-%d',datetime(substr(create_time,1,10), 'unixepoch', 'localtime')) as ct \n" +
            "FROM message) a where a.ct BETWEEN  strftime('%Y-%m-%d',?1) and  strftime('%Y-%m-%d',?2)  GROUP BY a.ct)", nativeQuery = true)
    List<Object> getResourcesCountByType(String beginTime, String endTime);*/

    @Query(value = "SELECT count(1) count1,ct day  ,'data' type  from (select strftime('%Y-%m-%d',datetime(substr(create_time,1,10), 'unixepoch', 'localtime')) as ct from message) a where a.ct BETWEEN  strftime('%Y-%m-%d',?1) and  strftime('%Y-%m-%d',?2)  GROUP BY a.ct", nativeQuery = true)
    List<Object> getResourcesCountByType(String beginTime, String endTime);

}
